/**
 * Copyright (c) 2017,安徽皖通科技股份有限公司 All rights reserved.
 *
 * @data:2017年10月15日
 * @author:MaShibo
 */
package com.wttech.dzzf.process.dao;

import com.wtkj.dao.SqlBuilder;
import com.wtkj.dao.impl.BaseDataTemplate;
import com.wtkj.dao.impl.ListResultSetExtractor;
import com.wtkj.dao.impl.ValueObjectSqlBuilder;
import com.wtkj.vo.AbstractValueObject;
import com.wtkj.vo.dzzf.base.kfwd.FundAccount;
import com.wttech.dzzf.process.model.CertKey;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 重复客户信息处理DAO
 *
 * @author MaShibo
 * @date 2017年10月15日
 */
@Repository
public class RepeatUserInfoDao extends BaseDataTemplate {


	/**
	 * YYMMDD-列名
	 */
	private static final String COLUMN_YMD = "YMD";

	/**
	 * 序号-列名
	 */
	private static final String COLUMN_SEQNO = "SEQNO";

	@Resource(name = "dzzfJdbcTemplate")
	private JdbcTemplate dzzfJdbcTemplate;

	@Resource(name = "jdbcTemplate")
	private JdbcTemplate testJdbcTemplate;

	/**
	 * 查询现存的用户信息中YMD-序列号键值对
	 *
	 * @return 序列号Map
	 */
	public Map<String, Integer> queryUserSeqNoMap() {
		String sql =
				"SELECT YMD, MAX(SEQNO) AS SEQNO\n" +
						"  FROM (SELECT SUBSTR(A.ID, 7, 6) AS YMD,\n" +
						"               TO_NUMBER(SUBSTR(A.ID, 13, 5)) + 1 AS SEQNO\n" +
						"          FROM KF_USERINFO A\n" +
						"        UNION ALL\n" +
						"        SELECT SUBSTR(K.ID, 7, 6) AS YMD, TO_NUMBER(SUBSTR(K.ID, 13, 5)) + 1\n" +
						"          FROM KF_COMPANYINFO K)\n" +
						" GROUP BY YMD";
		return dzzfJdbcTemplate.query(sql, rs -> {
			Map<String, Integer> seqNoMap = new HashMap<>();
			while (rs.next()) {
				seqNoMap.put(rs.getString(COLUMN_YMD), rs.getInt(COLUMN_SEQNO));
			}
			return seqNoMap;
		});
	}

	/**
	 * 查询所有重复的证件号和证件类型信息
	 *
	 * @return 重复的证件信息
	 */
	public List<CertKey> queryAllRepeatCertInfos() {
		String sql =
				"SELECT A.CERTIFINO, A.CERTIFITYPE " +
						"  FROM AC_FUNDACCOUNT A " +
						" WHERE A.ACCOUNTSTATUS = 1 " +
						" AND EXISTS (SELECT 1 " +
						"          FROM AC_CARDINFO C " +
						"          JOIN AC_ACCOUNT T " +
						"            ON C.ACCOUNTID = T.ACCOUNTID " +
						"         WHERE T.FUNDACCOUNTID = A.FUNDACCOUNTID AND C.ECARDSTATUS IN (1,2,3) AND C.USERID IS NULL) " +
						" GROUP BY A.CERTIFINO, A.CERTIFITYPE " +
						"HAVING COUNT(1) > 1 ";
		return dzzfJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(CertKey.class));
	}

	/**
	 * 根据证件信息查询账户信息集合
	 *
	 * @param certifiNo   证件号
	 * @param certifiType 证件类型
	 * @return 账户信息集合
	 */
	@SuppressWarnings("unchecked")
	public List<FundAccount> queryFundAccountByCertInfo(String certifiNo, Integer certifiType) {
		final String sql =
				"SELECT A.* " +
						"  FROM AC_FUNDACCOUNT A " +
						" WHERE A.ACCOUNTSTATUS = 1 " +
						"   AND EXISTS (SELECT 1 " +
						"          FROM AC_CARDINFO C " +
						"          JOIN AC_ACCOUNT T " +
						"            ON C.ACCOUNTID = T.ACCOUNTID " +
						"         WHERE T.FUNDACCOUNTID = A.FUNDACCOUNTID AND" +
						"            C.ECARDSTATUS IN (1,2,3) AND C.USERID IS  NULL)" +
						"   AND A.CERTIFITYPE = ? " +
						"   AND A.CERTIFINO = ?";

		return (List<FundAccount>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new FundAccount()), certifiType, certifiNo);
	}

	@SuppressWarnings("unchecked")
	public <T extends AbstractValueObject> List<T> queryTestList(AbstractValueObject obj) {
		SqlBuilder sqlBuilder = new ValueObjectSqlBuilder(obj);
		AbstractValueObject valueObject;
		try {
			valueObject = obj.getClass().newInstance();
		} catch (Exception e) {
			return new ArrayList<>();
		}
		return (List<T>) testJdbcTemplate.query(sqlBuilder.getSqlString(), sqlBuilder.getParameters(),
				new ListResultSetExtractor(valueObject));
	}

	/**
	 * 查询电子支付List集合信息
	 *
	 * @param obj 查询对象
	 * @return List集合
	 */
	@SuppressWarnings("unchecked")
	public <T extends AbstractValueObject> List<T> queryDzzfList(AbstractValueObject obj) {
		SqlBuilder sqlBuilder = new ValueObjectSqlBuilder(obj);
		AbstractValueObject valueObject;
		try {
			valueObject = obj.getClass().newInstance();
		} catch (Exception e) {
			return new ArrayList<>();
		}
		return (List<T>) dzzfJdbcTemplate.query(sqlBuilder.getSqlString(), sqlBuilder.getParameters(),
				new ListResultSetExtractor(valueObject));
	}

	public static void main(String[] args) {
		@SuppressWarnings("resource")
		ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
		RepeatUserInfoDao dao = context.getBean(RepeatUserInfoDao.class);
		List<FundAccount> fundAccounts = dao.queryFundAccountByCertInfo("612525198411286418", 0);
		System.out.println(fundAccounts);
	}
}
